package com.acc.mobileappr.scheduler.jobs;

import com.acc.mobileappr.constant.AppConstant;
import com.acc.mobileappr.entities.MstMApprDocEntity;
import com.acc.mobileappr.entities.MstMApprScreenEntity;
import com.acc.mobileappr.entities.MstMApprStructureEntity;
import com.acc.mobileappr.util.PushUtil;
import com.acc.mobileappr.util.Util;
import org.apache.log4j.Logger;
import org.eclipse.persistence.jpa.JpaEntityManager;
import org.eclipse.persistence.jpa.JpaHelper;
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
import org.eclipse.persistence.queries.DataReadQuery;
import org.eclipse.persistence.sessions.DatabaseRecord;
import org.eclipse.persistence.sessions.Session;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;

/**
 * New Document Job
 * Abiel@ICN
 */
public class NewDocumentJob implements Job {

    static private final Logger logger = Logger.getLogger(NewDocumentJob.class);

    private static final String PERSISTENCE_UNIT_NAME = "MarexPU";
    private static final EntityManagerFactory factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
    private static int sequenceNo = 1;

    /**
     * constructor
     */
    public NewDocumentJob() {

    }

    /**
     * Execute Job
     *
     * @param jobExecutionContext      job execution context
     * @throws JobExecutionException   job execution exception
     */
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {

        Util.print(" New document job executed on" + new Date());
        runNewProcedure();
        sequenceNo++;
    }


    /**
     * run new procedure
     */
    private void runNewProcedure() {

        EntityManager em;
        em = factory.createEntityManager();

        try {


            /*
            JpaEntityManager jpaEntityManager = JpaHelper.getEntityManager(em);
            Session session = jpaEntityManager.getActiveSession();

            PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
            call.setProcedureName("ACC_BBMX.p_generate_m_appr_transaction");
            call.addNamedOutputArgument("p_status", JDBCTypes.VARCHAR_TYPE);

            DataReadQuery query = new DataReadQuery();
            query.addArgument("p_status");

            query.setCall(call);

            List queryArgs = new ArrayList();
            queryArgs.add("");

            List results = (List) session.executeQuery(query, queryArgs);
            DatabaseRecord record = (DatabaseRecord) results.get(0);
            String status = (String) record.get("p_status");

            Util.print("ACC_BBMX.p_generate_m_appr_transaction (p_status =" + status + ")");

            if(status.equals("F")){
                em.close();
                return;
            }
            */

            //select decode (REQ_Type , 'DocType', '1', decode (REQ_Type , 'ScrList', '2',decode (REQ_Type , 'ScrNav', '3',decode (REQ_Type , 'ScrStruc', '4',decode (REQ_Type , 'ScrValue', '5', '6'))))) Type_no, req.* from trn_m_appr_request req where push_status = 'Q' order by Type_no


            Query q = em.createNativeQuery("select decode (req_type , 'DocType', '1', decode (req_type , 'ScrList', '2',decode (req_type , 'ScrNav', '3',decode (req_type , 'ScrStruc', '4',decode (REQ_Type , 'ScrValue', '5', '6'))))) type_no, " +
                    " req.* " +
                    " from trn_m_appr_request req " +
                    " where push_status = 'Q' order by Type_no");

            //Query q = em.createQuery("select req from TrnMApprRequestEntity req where req.pushStatus='Q' and req.retryRemain = '5' " +
            //        " order by req.pin");

            List<Object[]> list = q.getResultList();


            if (list.isEmpty()) {

                logger.debug("No Failed push data to be sent");

            } else {

                int n = list.size();
                int x=1;

                for (Object[] item : list) {

                    String userPin = (String) item[3];
                    String pushId = (String) item[7];
                    String data = (String) item[5];

                    Util.print("Sending Document no:"+x+" of "+n);
                    Util.print("Item="+userPin+","+pushId+","+data);

                    PushUtil pushUtil = new PushUtil();
                    pushUtil.pushData(userPin, pushId, data);

                    x++;
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
        }
    }

    /**
     * Run Procedure
     *
     * @deprecated
     */
    private void runProcedure() {

        //1. select * from mst_m_appr_doc where status='A'
        //and status_date=( select max(status_date) from mst_m_appr_doc where status='A')

        //2. loop get doc_id , description

        //2.1.select * from mst_m_appr_screen where doc_id='MRX' and status='A'
        //and status_date=(select max(status_date) from mst_m_appr_screen where status='A' and doc_id='MRX')

        //2.2 loop get screen_name and version and doc_id

        //2.2.1 select from mst_m_appr_structure where screen_name and version and doc_id

        //loop run query statement in mst_m_appr_structure

        //2.3

        //call procedure
        // p_doc_type = mst_doc.doc_id
        //p_version = mst_screen.version
        //p_bucket = mst_structure.bucket_id
        //p_screen = mst_screen.screen_name
        //p_content = mst_screen.content
        //p_sub_cat= mst_structure. index_on_screen
        //p_sub_screen = mst_structure.sub_category

        logger.info("START Running procedure..");

        List<MstMApprDocEntity> lstApprDoc = queryApprDoc();

        for (MstMApprDocEntity doc : lstApprDoc) {
            List<MstMApprScreenEntity> lstScreen = queryApprScreen(doc.getDocId());
            logger.info(doc.toString());

            for (MstMApprScreenEntity screen : lstScreen) {
                logger.info(screen.toString());
                List<MstMApprStructureEntity> lstStruct = queryApprStructure(screen.getScreenName(), screen.getVersion(), screen.getDocId());

                for (MstMApprStructureEntity struct : lstStruct) {
                    logger.info(struct.toString());
                    runStoredProcedure(struct.getQueryStatement()
                            , doc.getDocId()
                            , screen.getVersion()
                            , struct.getBucket()
                            , screen.getScreenName()
                            , struct.getContentId()
                            , struct.getIndexOnScreen()
                            , struct.getSubCategory());
                }
            }
        }

        logger.info("END Running procedure..");
    }

    /**
     * run oracle stored procedure
     *
     * @param procedureName   name of the procedure
     * @param docType         document type
     * @param version         version
     * @param bucket          bucket
     * @param screenName      screen name
     * @param subCat          sub cat
     * @param subScreen       sub screen
     * @deprecated
     */
    private void runStoredProcedure(String procedureName, String docType, String version, String bucket, String screenName, String contentId, String subCat, String subScreen) {
        logger.info(" START running stored procedure: " + procedureName);
        //if(procedureName==null){
        //     System.out.println("\t\t\t\tFAIL running null stored procedure.");
        //    return;
        // }

        EntityManager em;
        em = factory.createEntityManager();
        em.getTransaction().begin();

        String s = "BEGIN " +
                procedureName +
                " (p_doc_type=>" + docType +
                " , p_version=>" + version +
                ", p_bucket=>" + bucket +
                ", p_screen=>" + screenName +
                ", p_content=>" + contentId +
                ", p_sub_cat=>" + subCat +
                ", p_sub_screen=>" + subScreen +
                "); END;";

        System.out.println("\t\t\t\t" + s);

        Query q = em.createNativeQuery("BEGIN P_SCREEN_SUMMARY(?1, ?2, ?3, ?4, ?5,?6,?7 ); COMMIT; END;")
                .setParameter(1, docType)
                .setParameter(2, version)
                .setParameter(3, bucket)
                .setParameter(4, screenName)
                .setParameter(5, contentId)
                .setParameter(6, subCat)
                .setParameter(7, subScreen);

        try {

            q.executeUpdate();

        } catch (Exception e) {

            e.printStackTrace();

        }

        em.getTransaction().commit();
        em.close();
        System.out.println("\t\t\t\tOK");
    }

    /**
     * for handling in-out parameters
     *
     * @param procedureName       procedure name
     * @param docType             document type
     * @param version             version
     * @param bucket              bucket
     * @param screenName          screen name
     * @param contentId           content id
     * @param subCat              sub cat
     * @param subScreen           sub screen
     * @deprecated                deprecated function
     *
     */
    private void runPLSQLStoredProcedure(String procedureName, String docType, String version, String bucket, String screenName, String contentId, String subCat, String subScreen) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory(AppConstant.PERSISTENCE_UNIT_MAREX);
        EntityManager em = emf.createEntityManager();

        try {
            EntityTransaction trx = em.getTransaction();
            trx.begin();

            JpaEntityManager jpaEntityManager = JpaHelper.getEntityManager(em);
            Session session = jpaEntityManager.getActiveSession();

            PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
            call.setProcedureName(procedureName);
            call.addNamedArgument("p_doc_type", JDBCTypes.VARCHAR_TYPE);
            call.addNamedArgument("p_version", JDBCTypes.VARCHAR_TYPE);
            call.addNamedArgument("p_bucket", JDBCTypes.VARCHAR_TYPE);
            call.addNamedArgument("p_screen", JDBCTypes.VARCHAR_TYPE);
            call.addNamedArgument("p_content", JDBCTypes.VARCHAR_TYPE);
            call.addNamedArgument("p_sub_cat", JDBCTypes.VARCHAR_TYPE);
            call.addNamedArgument("p_sub_screen", JDBCTypes.VARCHAR_TYPE);

            DataReadQuery query = new DataReadQuery();
            query.addArgument("p_doc_type");
            query.addArgument("p_version");
            query.addArgument("p_bucket");
            query.addArgument("p_screen");
            query.addArgument("p_content");
            query.addArgument("p_sub_cat");
            query.addArgument("p_sub_screen");
            query.setCall(call);

            List<String> queryArgs = new ArrayList<String>();
            queryArgs.add(docType);
            queryArgs.add(version);
            queryArgs.add(bucket);
            queryArgs.add(screenName);
            queryArgs.add(contentId);
            queryArgs.add(subCat);
            queryArgs.add(subScreen);

            List results = (List) session.executeQuery(query, queryArgs);
            DatabaseRecord record = (DatabaseRecord) results.get(0);

            //print the out params here


            System.out.println(record);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
        }
    }


    /**
     * get document status date
     * @return
     */
    private Date getDocStatusDate() {
        EntityManager em = factory.createEntityManager();

        Query q = em.createNativeQuery("select sysdate from dual ");

        List<Date> l = (List<Date>) q.getResultList();
        if (l.isEmpty()) {
            return null;
        }

        em.close();
        return l.get(0);
    }

    /**
     * get screen status date
     * @param docId
     * @return
     */
    private Date getScreenStatusDate(String docId) {
        EntityManager em = factory.createEntityManager();
        Query q = em.createNativeQuery("select max(status_date) from mst_m_appr_screen where status='A'and doc_id='" + docId + "'");
        List<Date> l =  q.getResultList();

        if (l.isEmpty()) {
            return null;
        }

        em.close();
        return l.get(0);
    }


    /**
     * Query Approval Document
     *
     * @return list of MstMApprDocEntity
     */
    private List<MstMApprDocEntity> queryApprDoc() {
        Date statusDate = getDocStatusDate();

        EntityManager em = factory.createEntityManager();
        Query q = em.createQuery("select d from MstMApprDocEntity d where d.status = 'A' and d.statusDate <= ?1").setParameter(1, statusDate);
        List<MstMApprDocEntity> l = q.getResultList();
        em.close();

        return l;
    }


    /**
     * Query approval screen
     *
     * @param docId   document id
     * @return        list of MstMApprScreenEntity
     */
    private List<MstMApprScreenEntity> queryApprScreen(String docId) {
        Date statusDate = getScreenStatusDate(docId);
        EntityManager em = factory.createEntityManager();

        Query q = em.createQuery("select s from MstMApprScreenEntity s where s.status = 'A' and s.statusDate=?1 and s.docId=?2")
                .setParameter(1, statusDate)
                .setParameter(2, docId);


        List<MstMApprScreenEntity> l =  q.getResultList();
        em.close();
        return l;
    }

    /**
     * Query approval structure
     *
     * @param screenName  screen name
     * @param version     version
     * @param docId       document id
     * @return            list of MstMApprStructureEntity
     */
    private List<MstMApprStructureEntity> queryApprStructure(String screenName, String version, String docId) {

        EntityManager em = factory.createEntityManager();
        Query q = em.createQuery("select s from MstMApprStructureEntity  s where s.screenName =?1 and s.version = ?2 and s.docId = ?3")
                .setParameter(1, screenName)
                .setParameter(2, version)
                .setParameter(3, docId);

        List<MstMApprStructureEntity> l = q.getResultList();
        em.close();
        return l;

    }
}
